if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CleanPreviousProtocolSubmissions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CleanPreviousProtocolSubmissions]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			CleanPreviousProtocolSubmissions
* Purpose:		Removed prior Protocol Submissions from ProtocolSubmission 
*					due to Rex's cloning of Protocols. Note that these will
*					only be dupes for EIRB created protocols.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
***************************************************************************/
CREATE PROCEDURE dbo.CleanPreviousProtocolSubmissions
AS
BEGIN

	SET NOCOUNT ON


	BEGIN 
		Truncate table PendingApplications_FCT
	END

	BEGIN
		Delete ProtocolBoardReview where ProtocolSubmissionId in (Select Id from protocolSubmission 
				Where RexIRBStatusId in (Select RexIRBStatus from (
		select Distinct(SubQ.Id) as ProtocolId, SubQ.FormsId, Min(RexIRBStatusID) as RexIRBStatus
			 from ProtocolSubmission S inner join (
		Select S.FormsId, P.Id from ProtocolSubmission S
			inner join Protocol P
			on P.Id = S.ProtocolId
			Where P.Id in (select Distinct(ProtocolId) from ProtocolProcess)
			group by P.Id, S.FormsId
			having count(FormsId) > 1 ) SubQ
			on S.ProtocolId = SubQ.Id and S.FormsId = SubQ.FormsId
			Group by SubQ.Id, SubQ.FormsId ) as Query ))
	END

/**	BEGIN
		Delete PendingApplications_FCT where ProtocolSubmissionId in (Select Id from protocolSubmission 
				Where RexIRBStatusId in (Select RexIRBStatus from (
		select Distinct(SubQ.Id) as ProtocolId, SubQ.FormsId, Min(RexIRBStatusID) as RexIRBStatus
			 from ProtocolSubmission S inner join (
		Select S.FormsId, P.Id from ProtocolSubmission S
			inner join Protocol P
			on P.Id = S.ProtocolId
			Where P.Id in (select Distinct(ProtocolId) from ProtocolProcess)
			group by P.Id, S.FormsId
			having count(FormsId) > 1 ) SubQ
			on S.ProtocolId = SubQ.Id and S.FormsId = SubQ.FormsId
			Group by SubQ.Id, SubQ.FormsId ) as Query ))
	END
**/
	BEGIN
		Delete ProtocolSubmission where Id in (Select Id from protocolSubmission 
				Where RexIRBStatusId in (Select RexIRBStatus from (
		select Distinct(SubQ.Id) as ProtocolId, SubQ.FormsId, Min(RexIRBStatusID) as RexIRBStatus
			 from ProtocolSubmission S inner join (
		Select S.FormsId, P.Id from ProtocolSubmission S
			inner join Protocol P
			on P.Id = S.ProtocolId
			Where P.Id in (select Distinct(ProtocolId) from ProtocolProcess)
			group by P.Id, S.FormsId
			having count(FormsId) > 1 ) SubQ
			on S.ProtocolId = SubQ.Id and S.FormsId = SubQ.FormsId
			Group by SubQ.Id, SubQ.FormsId ) as Query ))
	END


	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
